create database DBHOTEL
go

use DBHOTEL
go
 
create table empleado (
idEmpleado int not null identity primary key,
nombre varchar (20) not null,
apellidos varchar (30)not null,
dni int not null unique ,
direccion varchar(80) not null,
tipo_empleado varchar(15) not null
)
go

create table mesa(
idMesa int not null identity primary key,
observacion varchar(20) not null,
)

create table plato(
idPlato int not null identity primary key,
nombre varchar(20) not null,
precio money not null
)
go

create table refresco(
idrefresco int identity primary key,
nombre varchar(20) not null,
precio money not null
)
go

create table pedido (
idPedido int not null primary key identity ,
idEmpleado int references empleado not null,
idMesa int references mesa not null,
idPlato int references plato not null,
idRefresco int references refresco not null,
observacion varchar(20) null 
)

create table factura (
idFactura int not null primary key identity,
idPedido int references pedido not null,
idPlato int references plato not null,
idRefresco int references refresco not null,
total money null,
observacion varchar(20)null,
)
go

insert into mesa values ('1')
insert into mesa values ('2')
select * from mesa
--
insert into plato values ('Arroz com pollo', 4.5)
insert into plato values ('Pollo frito', 5)
select * from plato
--
insert into refresco values ('Limonada', 1.5)
insert into refresco values ('Chicha morada', 2.0)
select * from refresco
--

select * from empleado

select sum (p.precio+r.precio) as 'Total' 
from  plato p,refresco r , factura f
where f.idFactura = 1 
--where v.nom_vend like '[d-l]%' 

